package org.zn.mvcbase.repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.zn.mvcbase.common.utilities.DateFormatUtility;

/**
 * 
 * @ProjectName:91isoft_oa
 * @ClassName:BaseCommonSQLProvider.java
 * @Description: 动态SQL语句拼接构造器辅助类
 * @Copyright: Copyright (c) 2016

 * @author: Lan Yuan
 * @email: lanyuan@91isoft.com 
 * @date 2016年3月7日 下午4:14:36
 * @version V1.0
 */
public class BaseCommonSQLProvider {
	
	/**
	 * @Description: 动态SQL语句拼接,调用前必须详读调用规范！
	 * 							 参数Key规范及使用说明
	 * 							 $like   模糊查询
	 *                          $date1（自动大于等于）  起始时间
	 *                          $date2（自动小于等于）  截止时间
	 *                          $dy       大于
	 *                          $xy       小于
	 *                          $dydy   大于等于
	 *                          $xydy    小于等于
	 *                          $bdy     不等于
	 * @MethodName:buildDynamicConditionQuerySQL
	 * @author: Lan Yuan
	 * @email: lanyuan@91isoft.com 
	 * @date 2016年3月7日 下午4:16:44
	 * @version V1.0
	 * @param column_names 查询字段集合，逗号分割
	 * @param table_name 真实表名称
	 * @param queryParams  参数映射集合
	 * @param operaterChar 逻辑运算符   默认为AND
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static String buildDynamicConditionQuerySQL(Map<String, Object> objectMap){
		String table_name = (String) objectMap.get("table_name");  
		String operaterChar = (String) objectMap.get("operaterChar");
		String column_names = (String) objectMap.get("column_names");
		String customSQLsegment = (String) objectMap.get("customSQLsegment");
		Map<String, String> queryParams = (Map<String, String>) objectMap.get("queryParams");
		if(operaterChar==null||"".equals(operaterChar)){
			operaterChar = " AND ";
		}else{
			operaterChar = " "+operaterChar.trim()+" ";
		}
		StringBuilder stringBuilder = new StringBuilder();
		stringBuilder.append("SELECT ");
		if(null!=column_names&&!"".equals(column_names)&&!"*".trim().equals(column_names)){
			stringBuilder.append(column_names+" ");
		}else{
			stringBuilder.append("* ");
		}
		stringBuilder.append(" FROM ");
		stringBuilder.append(table_name+" ");
		
		
		
		
		boolean hasWhere = false;
		
		if(customSQLsegment!=null&&!"".equals(customSQLsegment)){
			stringBuilder.append("WHERE ");
			stringBuilder.append(customSQLsegment);
			hasWhere = true;
		}
		
		
		
		
		if(queryParams!=null&&queryParams.size()>0){
			if(!hasWhere){
				stringBuilder.append("WHERE ");
			}else{
				stringBuilder.append(operaterChar);
			}
			List<String> queryCondition= new ArrayList<String>();
			for (String fieldName : queryParams.keySet()) {
				String fieldValue = queryParams.get(fieldName),queryExpression = "";
				String[] split = fieldName.split("\\$");
				if(split.length>1){
					fieldName = split[0];
					String handlerChar = split[1];
					handlerChar = handlerChar.toUpperCase();
					switch (handlerChar) {
					case "LIKE":
						queryExpression=fieldName+" LIKE "+"'%"+fieldValue+"%'";
						break;
					case "DATE1":
						fieldValue = DateFormatUtility.appendSecondDateString(fieldValue, true);
						queryExpression=fieldName+" >= '"+fieldValue+"'";
						break;
					case "DATE2":
						fieldValue = DateFormatUtility.appendSecondDateString(fieldValue, false);
						queryExpression=fieldName+" <=  '"+fieldValue+"'";
						break;
					case "DY":
						queryExpression=fieldName+" >  '"+fieldValue+"'";
						break;
					case "XY":
						queryExpression=fieldName+" <  '"+fieldValue+"'";
						break;
					case "DYDY":
						queryExpression=fieldName+" >=  '"+fieldValue+"'";
						break;
					case "XYDY":
						queryExpression=fieldName+" <=  '"+fieldValue+"'";
						break;
					case "BDY":
						queryExpression=fieldName+" <>  '"+fieldValue+"'";
						break;
					default:
						break;
					}
				}else{
					queryExpression = fieldName+" =  '"+fieldValue+"'";
				}
				queryCondition.add(queryExpression);
			}
			
			
			stringBuilder.append(StringUtils.join(queryCondition,operaterChar));
		}
		return stringBuilder.toString().toUpperCase();
	}
	
//	public static void main(String[] args) {
//		Map<String, String> map = new HashMap<String,String>();
//		map.put("abc", "abc_value");
//		map.put("abc_f1$like", "abc_value1");
//		map.put("abc_f2$date1", "abc_value2");
//		map.put("abc_f3$date2", "abc_value3");
//		String buildDynamicConditionQuerySQL = buildDynamicConditionQuerySQL("tableName", "", map, "AND");
//		System.out.println(buildDynamicConditionQuerySQL);
//	}
	
}
